﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

CREATE FUNCTION [dbo].[bhf_NemoFallbackTranslateGet](
	@FieldId			INT,
	@Record				INT,
	@Language			NCHAR(5),
	@FallbackLanguage	NCHAR(5),
	@DefaultValue		NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= @FieldId
				AND record_id	= @Record
				AND lang_code	= @Language),
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= @FieldId
				AND record_id	= @Record
				AND lang_code	= @FallbackLanguage), @DefaultValue)

END

GO

CREATE FUNCTION [dbo].[bhf_NemoFallbackTranslateGetByPrefix](
	@RecordIdx			INT,
	@Prefix				NVARCHAR(100),
	@Language			NCHAR(5),
	@FallbackLanguage	NCHAR(5),
	@DefaultValue		NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN
	
	DECLARE
		@Group			NVARCHAR(50),
		@Column			NVARCHAR(50),
		@GroupId		INT,
		@ColumnId		INT
		
	SET @Group = SUBSTRING(@Prefix, 0, PATINDEX('%.%', @Prefix))
	SET @Column = SUBSTRING(@Prefix, PATINDEX('%.%', @Prefix) + 1, 50)
	
	SELECT @GroupId = id FROM n_Tree WHERE name = @Group AND parent = 0
	SELECT @ColumnId = id FROM n_Tree WHERE name = @Column AND parent = @GroupId
	
	RETURN (SELECT [dbo].[bhf_NemoFallbackTranslateGet] (
				@ColumnId,
				@RecordIdx,
				@Language,
				@FallbackLanguage,
				@DefaultValue))
	
END
